-- 费用报表基础函数：根据年度、科目获取费用数据
alter function fun_czly_GetFeeData(
    @year int,
    @accno varchar(10),
    @orgId int
) returns @result table(
    序号 int,
    费用项目 varchar(55),
    一月 decimal(18, 6),
    二月 decimal(18, 6),
    三月 decimal(18, 6),
    四月 decimal(18, 6),
    五月 decimal(18, 6),
    六月 decimal(18, 6),
    七月 decimal(18, 6),
    八月 decimal(18, 6),
    九月 decimal(18, 6),
    十月 decimal(18, 6),
    十一月 decimal(18, 6),
    十二月 decimal(18, 6),
    累计 decimal(18, 6)
)
as
begin
    declare @basedata table (
        FSeq int,
        FPeriod int,
        FName varchar(100),
        FDebit decimal(18, 6),
        FCredit decimal(18, 6)
    )

    insert into @basedata
    select ep.F_PAEZ_ClsSeq, FPeriod, ep.F_PAEZ_ClsName, FDEBIT, FCREDIT
        -- case FDEBIT when 0 then 0 else FCREDIT end
    from V_CN_VOUCHER v
    inner join V_CN_VOUCHERENTRY ve on ve.FVOUCHERID=v.FVOUCHERID
    inner join T_BD_ACCOUNT a on a.FACCTID=ve.FACCOUNTID
    inner join T_BD_FLEXITEMDETAILV fd on fd.FID=ve.FDetailID
    inner join T_BD_EXPENSE ep on ep.FEXPID=fd.FFLEX9
    where a.FNumber=@accno and FYear=@year and v.FACCTORGID=@orgId 
    and dbo.fun_czly_IsCarryForward(v.FVOUCHERID)=0

    
    if @year = 2021 and @orgId = 100008
    begin
        -- 1月份上手期初销售数据
        if @accno = '6601'
        begin
            insert into @basedata values(1, 1, '工资', 543775.38, 0)
            insert into @basedata values(2, 1, '养老保险费', 105022.24, 0)
            insert into @basedata values(3, 1, '医疗保险费', 54461.13, 0)
            insert into @basedata values(4, 1, '住房公积金', 61078.12, 0)
            insert into @basedata values(5, 1, '福利费', 16901.1, 0)
            insert into @basedata values(7, 1, '工会经费', 9325.59, 0)
            insert into @basedata values(9, 1, '差旅费', 67853.78, 0)
            insert into @basedata values(10, 1, '办公费', 26059.03, 0)
            insert into @basedata values(12, 1, '业务招待费', 11119, 0)
            insert into @basedata values(15, 1, '运输费', 145917.38, 0)
            insert into @basedata values(30, 1, '包装费', 11180.72, 0)
            insert into @basedata values(32, 1, '商业折扣', 1290000, 0)
        end
        -- 1月份上手期初管理数据
        else if @accno = '6602'
        begin
            insert into @basedata values(1, 1, '工资', 1372332.14, 0)
            insert into @basedata values(2, 1, '养老保险费', -295993.52, 0)
            insert into @basedata values(3, 1, '医疗保险费', 54356.94, 0)
            insert into @basedata values(4, 1, '住房公积金', 61885, 0)
            insert into @basedata values(5, 1, '福利费', 150015.3, 0)
            insert into @basedata values(7, 1, '工会经费', 11484.58, 0)
            insert into @basedata values(8, 1, '辞退福利', 98598, 0)
            insert into @basedata values(9, 1, '差旅费', 30012.44, 0)
            insert into @basedata values(10, 1, '办公费', 25463.33, 0)
            insert into @basedata values(11, 1, '水电费', 55625.72, 0)
            insert into @basedata values(12, 1, '业务招待费', 3158.9, 0)
            insert into @basedata values(13, 1, '折旧费', 51785.84, 0)
            insert into @basedata values(16, 1, '保险费', 4309.1, 0)
            insert into @basedata values(22, 1, '汽车费用', 6736.7, 0)
            insert into @basedata values(33, 1, '修理费', 12950.38, 0)
            insert into @basedata values(36, 1, '警卫消防费', 1020.48, 0)
            insert into @basedata values(38, 1, '租赁费', 75740.97, 0)
            insert into @basedata values(40, 1, '退休人员费用', 201209.66, 0)
            insert into @basedata values(41, 1, '离休人员经费', 49513, 0)
            insert into @basedata values(42, 1, '劳动保护费', 653.78, 0)
            insert into @basedata values(60, 1, '其他', 35794.16, 0)
        end
        -- 研发1月份
        else if @accno = '6604'
        begin
            insert into @basedata values(1, 1, '工资', 317890.00, 0)
            insert into @basedata values(2, 1, '养老保险费', 81619.51, 0)
            insert into @basedata values(4, 1, '住房公积金', 29324.00, 0)
            insert into @basedata values(5, 1, '福利费', 10800.00, 0)
            insert into @basedata values(7, 1, '工会经费', 6121.8, 0)
            insert into @basedata values(10, 1, '办公费', 215.38, 0)
            insert into @basedata values(13, 1, '折旧费', 184008.39, 0)
            insert into @basedata values(42, 1, '专利及软件费', 5377.36, 0)
            insert into @basedata values(44, 1, '新品试制成本', 464416.32, 0)
            insert into @basedata values(43, 1, '测试注册费', 11660.38, 0)
        end
    end

    insert into @result
    select FSeq 序号, FName 费用项目,
        sum(FJanFee) 一月, sum(FFebFee) 二月, sum(FMarFee) 三月, sum(FAprFee) 四月,
        sum(FMayFee) 五月, sum(FJunFee) 六月, sum(FJulFee) 七月, sum(FAugFee) 八月,
        sum(FSeptFee) 九月, sum(FOctFee) 十月, sum(FNovFee) 十一月, sum(FDecFee) 十二月,
        sum(FTotal) 累计
    from (
        select FSeq, FName, 
            sum(FDEBIT)-sum(FCREDIT) FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=1
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, sum(FDEBIT)-sum(FCREDIT) FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=2
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, sum(FDEBIT)-sum(FCREDIT) FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=3
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, 0 FMarFee, sum(FDEBIT)-sum(FCREDIT) FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=4
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            sum(FDEBIT)-sum(FCREDIT) FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=5
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, sum(FDEBIT)-sum(FCREDIT) FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=6
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, sum(FDEBIT)-sum(FCREDIT) FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=7
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, sum(FDEBIT)-sum(FCREDIT) FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=8
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            sum(FDEBIT)-sum(FCREDIT) FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=9
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, sum(FDEBIT)-sum(FCREDIT) FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=10
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, sum(FDEBIT)-sum(FCREDIT) FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=11
        group by FSeq, FName
        union all
        select FSeq, FName, 
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, sum(FDEBIT)-sum(FCREDIT) FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
        from @basedata
        where FPeriod=12
        group by FSeq, FName
    )t
    group by FSeq, FName

    return 

end
-- select * from dbo.fun_czly_GetFeeData(2021, '6602', 100008)